package com.yixun.qfbao.service.impl;

import com.beust.jcommander.internal.Lists;
import com.yixun.qfbao.constant.StringConstant;
import com.yixun.qfbao.dao.DepartmentDao;
import com.yixun.qfbao.dao.OtherCompanyDao;
import com.yixun.qfbao.mapper.PremisesImageMapper;
import com.yixun.qfbao.model.*;
import com.yixun.qfbao.model.dto.*;
import com.yixun.qfbao.model.enums.*;
import com.yixun.qfbao.service.*;
import com.yixun.qfbao.util.DateStyle;
import com.yixun.qfbao.util.DateUtils;
import com.yixun.qfbao.util.PasswordUtil;
import com.yixun.qfbao.util.PatternUtil;
import com.yixun.qfbao.util.exception.TServerException;
import com.yixun.qfbao.xf.model.Premises;
import com.yixun.qfbao.xf.model.dto.ApartmentLayoutDiagramDto;
import com.yixun.qfbao.xf.model.dto.CommissionJsonDto;
import com.yixun.qfbao.xf.model.dto.PremisesDto;
import com.yixun.qfbao.xf.model.dto.PremisesDynamicDto;
import com.yixun.qfbao.xf.model.enums.PremisesStatusEnum;
import com.yixun.qfbao.xf.model.enums.ReportWayEnum;
import com.yixun.qfbao.xf.model.enums.ResultEnum;
import com.yixun.qfbao.xf.model.enums.ShelvesStatusEnum;
import com.yixun.qfbao.xf.service.ApartmentLayoutDiagramService;
import com.yixun.qfbao.xf.service.PremisesDynamicService;
import com.yixun.qfbao.xf.service.PremisesService;
import net.sf.json.JSONArray;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 *
 * @author qf
 */
@Service
public class UploadExcelServiceImp implements UploadExcelService {
	private static Logger log = Logger.getLogger(UploadExcelServiceImp.class);
	
	@Autowired
	private DepartmentDao departmentDao;
	@Autowired
	private PartnerInfoService partnerInfoService;
	@Autowired
	private ChannelRoleService channelRoleService;
    @Autowired
    private CompanyService companyService;
	@Autowired
	private ImportErrorService importErrorService;
	@Autowired
    private CompanyRelationService companyRelationService;
	@Autowired
    private PremisesService premisesService;
	@Autowired
    private PremisesRelationService premisesRelationService;
	@Autowired
    private AreaService areaService;

	@Autowired
	private PremisesRelationExternalService premisesRelationExternalService;

	@Autowired
	private OtherCompanyDao otherCompanyDao;

	@Autowired
	private ApartmentLayoutDiagramService apartmentLayoutDiagramService;

	@Autowired
	private PremisesDynamicService premisesDynamicService;

	@Autowired
	private PremisesDynamicExternalService premisesDynamicExternalService;

	@Autowired
	private PremisesImageMapper premisesImageMapper;
	
	@Transactional
	@Override
	public Map<String,String> uploadExcel(Long parentId, PartnerInfo currLoginUser, InputStream file, String fileType){
		Map<String,String> returnMap = new HashMap<>();
		Map<String, List<DepartmentDto>> resMap = read(file, fileType);
		if(! resMap.containsKey("success") && resMap.containsKey("fail")){
			returnMap.put("result","0");
			returnMap.put("msg","excel获取数据失败");
			return returnMap;
		}

		//保存正确的数据
    	List<DepartmentDto> list =  resMap.get("success");
    	//保存问题数据
    	List<DepartmentDto> failList = resMap.get("fail");
    	log.debug("excel导入成功Success，准备插入数据库");
    	
    	//查询电话号码重复的数据
    	int orgNum = 0;
		List<Department> orgList = distinctOrgNum(list, currLoginUser.getHigherid());
		if(orgList != null){
			orgNum = orgList.size();
		}
		
		int successCount = list.size() - orgNum; //成功条数
    	int failCount = failList.size() + orgNum; //失败条数
    	int total = successCount+failCount; //总条数
    	log.debug("失败条数==="+failCount+"; 成功条数==="+successCount+"; 总条数==="+total);
    	
    	for(int i = 0; i < list.size(); i++){
    		DepartmentDto dto = (DepartmentDto)list.get(i);
    		
    		//过滤重复部门名称
			boolean flag = true;
    		for(int l = 0; l < orgNum; l++){
    			Department dt = (Department)orgList.get(l);
    			if(dt.getDepartmentName().equals(dto.getDepartmentName())){
					//当有重复时号码时，跳出循环
					flag = false;
					dto.setFailure("部门名称重复；");
					failList.add(dto);
					break;
				}
    		}
    		if(flag){
    			dto.setParentId(parentId);
        		dto.setMerchantId(currLoginUser.getHigherid());
        		dto.setCreateUserId(currLoginUser.getId());
        		dto.setDelFlag(DelFlagEnum.UNDEL.getNo());
				departmentDao.create(dto);
    		}
    	}
    	
    	//导入失败数据
    	String uuid = UUID.randomUUID().toString();
		for(int i = 0; i < failList.size(); i++){
			DepartmentDto dto = (DepartmentDto)failList.get(i);
			ImportErrorDto entity = new ImportErrorDto();
			entity.setImpId(uuid);
			entity.setImportType("1");
			entity.setUnitName(dto.getDepartmentName());
			entity.setUnitCode(dto.getDepartmentCode());
			entity.setRemark(dto.getRemark());
			entity.setFailure(dto.getFailure());
			entity.setMerchantId(currLoginUser.getHigherid());
			importErrorService.create(entity);
    	}

		returnMap.put("result", ResultEnum.SUCCESS.getNo());
    	StringBuffer msg = new StringBuffer();
    	msg.append("批量导入完成，共").append(total)
    	   .append("条,成功导入").append(successCount)
    	   .append("条，失败").append(failCount).append("条");
    	returnMap.put("msg",msg.toString());
    	if(failList.size() > 0){
			returnMap.put("data",uuid);
    	}
		
		return returnMap;
	}
	
	/**
	 * 获取excel数据
	 */
	public Map<String, List<DepartmentDto>> read(InputStream file, String fileType){

		Map<String, List<DepartmentDto>> map = new HashMap<String, List<DepartmentDto>>();
		//保存正确的数据
    	List<DepartmentDto> list = new ArrayList<DepartmentDto>();
    	//保存问题数据
    	List<DepartmentDto> failList = new ArrayList<DepartmentDto>();
		
		try {
			Workbook wb = null;
			if (fileType.equals("xls")) {
				wb = new HSSFWorkbook(file);
			} else if (fileType.equals("xlsx")) {
				wb = new XSSFWorkbook(file);
			} else {
				log.debug("您上传的excel格式不正确");
			}
			Sheet sheet1 = wb.getSheetAt(0);
			log.debug("excel上传行数===="+sheet1.getLastRowNum());
			
			Map<String, String> orgNameMap = new HashMap<String, String>(); //用于判断excel中的号码是否重复
			for (int i = 1; i <= sheet1.getLastRowNum(); i++) {
				Row row = sheet1.getRow(i);
				DepartmentDto dto = new DepartmentDto();
				String failMsg = "";
				
				//部门名称
				if (row.getCell(0) != null) {
					Cell cell = row.getCell(0);
					cell.setCellType(Cell.CELL_TYPE_STRING);
					String orgName = cell.getStringCellValue();
					if(StringUtils.isNotBlank(orgNameMap.get(orgName))){
						failMsg += "部门名称重复；";
					}
					dto.setDepartmentName(orgName);
					orgNameMap.put(orgName, orgName);
				} else {
					failMsg += "部门名称不能为空;";
				}
				//部门编号
				if (row.getCell(1) != null) {
					dto.setDepartmentCode(row.getCell(1).toString());
				} 
				//备注
				if (row.getCell(2) != null){
					dto.setRemark(row.getCell(2).toString());
				}

				if (!"".equals(failMsg)) {
					log.debug("excel错误信息==="+failMsg);
					dto.setFailure(failMsg);
					failList.add(dto);
				} else {
					list.add(dto);
				}
			}
			map.put("success", list);
	        map.put("fail", failList);
		} catch (Exception e) {
			log.error("获取excel数据失败: {}", e);
		}finally{
			try {
				file.close();
			} catch (IOException e) {
				log.error("isExistsInM2 error: {}",e);
			}
		}
        
        return map;
	}
	/**
	 * 获取excel数据
	 */
	public List<ImportCooOrgDto> readCooOrgExcel(InputStream file, String fileType) throws Exception{

    	List<ImportCooOrgDto> list = new ArrayList<>();

		try {
			Workbook wb = null;
			if (null != fileType && fileType.equals("xls")) {
				wb = new HSSFWorkbook(file);
			} else if (null != fileType && fileType.equals("xlsx")) {
				wb = new XSSFWorkbook(file);
			} else {
				throw new TServerException("您上传的excel格式不正确");
			}
			Sheet sheet1 = wb.getSheetAt(0);
			log.debug("excel上传行数===="+sheet1.getLastRowNum());

			//公司名称、地址、省份不能为空   姓名、手机号不能为空  初始密码x1234567  状态：在用
			for (int i = 3; i <= sheet1.getLastRowNum(); i++) {
				Row row = sheet1.getRow(i);
				ImportCooOrgDto sucDto = new ImportCooOrgDto();
				//公司0、名店1、组别2、地址3、省份4、市5、姓名6、手机号7、是否管理员8
				//公司名称
				if (Objects.nonNull(row.getCell(0))) {
                    sucDto.setCompanyName(row.getCell(0).toString());
				}
				if (Objects.nonNull(row.getCell(1))) {
					sucDto.setPrincipalName(row.getCell(1).toString());
				}
				if (Objects.nonNull(row.getCell(2))) {
					Cell cell = row.getCell(2);
					cell.setCellType(CellType.STRING);
					sucDto.setPrincipalPhone(cell.getStringCellValue());
				}
                //门店名称
                if (Objects.nonNull(row.getCell(3))) {
                    sucDto.setStoreName(row.getCell(3).toString());
                }
                //组别名称
                if (Objects.nonNull(row.getCell(4))){
                    sucDto.setGroupName(row.getCell(4).toString());
                }
                //地址
                if (row.getCell(5) != null) {
                    sucDto.setAddress(row.getCell(5).toString());
                }
                //省份
                if (Objects.nonNull(row.getCell(6))) {
                    sucDto.setProvince(row.getCell(6).toString());
                }
                //市
                if (Objects.nonNull(row.getCell(7))) {
                    sucDto.setCity(row.getCell(7).toString());
                }
                //姓名
                if (Objects.nonNull(row.getCell(8))) {
                    sucDto.setUserName(row.getCell(8).toString());
                }
                //手机
                if (Objects.nonNull(row.getCell(9))) {
                    Cell cell = row.getCell(9);
					cell.setCellType(CellType.STRING);
                    sucDto.setPhone(cell.getStringCellValue());
                }
                //是否管理员
                if (Objects.nonNull(row.getCell(10))) {
                    sucDto.setIsAdmin(row.getCell(10).toString());
                }
                list.add(sucDto);
			}
		} catch (Exception e) {
			log.error("获取excel数据失败: {}", e);
		}finally{
			try {
				file.close();
			} catch (IOException e) {
				log.error("isExistsInM2 error: {}",e);
			}
		}

        return list;
	}

	/**
	 * 查询是否已有相同名称的部门
	 * @param list
	 * @return
	 */
	public List<Department> distinctOrgNum(List<DepartmentDto> list, Long merchantId){
		List<String> departmentNames = new ArrayList<String>();
		for(DepartmentDto dto : list){
			departmentNames.add(dto.getDepartmentName());
		}
		DepartmentDto entity = new DepartmentDto();
		entity.setDepartmentNames(departmentNames);
		entity.setMerchantId(merchantId);
		List<Department> depList = departmentDao.distinctOrgNum(entity);
		return depList;
	}
	
	@Transactional
	@Override
	public Map<String,String> uploadExcelUser(Long userId, InputStream file, String fileType){
		Map<String,String> returnMap = new HashMap<String, String>();
		Map<String, List<PartnerInfoDto>> resMap = readUser(file, fileType);
		if(! resMap.containsKey("success") && resMap.containsKey("fail")){
			returnMap.put("result", ResultEnum.FAIL.getNo());
			returnMap.put("msg","excel获取数据失败");
			return returnMap;
		}
		//保存正确的数据
    	List<PartnerInfoDto> list = resMap.get("success");
    	//保存问题数据
    	List<PartnerInfoDto> failList = resMap.get("fail");
    	log.debug("excel导入成功Success，准备插入数据库");
    	
    	//当前登录用户
    	PartnerInfo currLoginUser = partnerInfoService.selectById(userId);

        int successCount = 0;
        int failCount = 0;
        int failNum = 0; //记录失败次数
        int parNum = 0; // 电话号码重复统计
    	if (list != null && list.size() > 0) {
            //查询电话号码重复的数据
            List<PartnerInfo> parList = distinctPhoneNum(list, currLoginUser.getHigherid());
            if(parList != null){
                parNum = parList.size();
            }

            //查询公司下全部部门
            Map<String, Long> orgMap = allOrgMap(list, currLoginUser.getHigherid());
            //查询公司下全部角色
            Map<String, Long> roleMap = allRoleMap(list, currLoginUser.getHigherid());

            successCount = list.size() - parNum; //成功条数


            for(int i = 0; i < list.size(); i++){
                PartnerInfoDto dto = (PartnerInfoDto)list.get(i);

                //过滤重复手机号
                boolean flag = true;
                String failMsg = "";
                for(int l = 0; l < parNum; l++){
                    PartnerInfo dt = (PartnerInfo)parList.get(l);
                    if(dt.getPhone().equals(dto.getPhone())){
                        //当有重复时号码时，跳出循环
                        flag = false;
                        failMsg += "联系电话重复；";
                        break;
                    }
                }
                //查询部门
                if(orgMap.get(dto.getUnitName()) == null){
                    if("".equals(failMsg)){
                        failNum++;
                    }
                    flag = false;
                    failMsg += "未查到对应部门；";
                }
                //查询角色
                if(roleMap.get(dto.getRoleName()) == null){
                    if("".equals(failMsg)){
                        failNum++;
                    }
                    flag = false;
                    failMsg += "未查到对应角色；";
                }
                if(flag){
                    dto.setPassword(PasswordUtil.md5Hex(dto.getPassword()));
                    dto.setHigherid(currLoginUser.getHigherid());
                    dto.setUserRoleId(Long.valueOf(roleMap.get(dto.getRoleName()).toString()));
                    dto.setDeptId(Long.parseLong(orgMap.get(dto.getUnitName()).toString()));
                    dto.setAccountType(MechanismAccountTypeEnum.individual.getNo());
                    dto.setStatus(MechanismStatusEnum.enable.getNo());
                    partnerInfoService.create(dto);
                }else{
                    //输出错误信息（可保存入库，或导出excel）
                    dto.setFailure(failMsg);
                    failList.add(dto);
                }
            }
        }
        failCount = failList.size() + parNum; //失败条数
    	//导入失败数据
    	String uuid = UUID.randomUUID().toString();
        if (failList != null) {
            for(int i = 0; i < failList.size(); i++){
                PartnerInfoDto dto = (PartnerInfoDto)failList.get(i);

                ImportErrorDto entity = new ImportErrorDto();
                entity.setImpId(uuid);
                entity.setImportType("2");
                entity.setUserName(dto.getAccountName());
                entity.setUserPhone(dto.getPhone());
                entity.setUnitName(dto.getUnitName());
                entity.setRoleName(dto.getRoleName());
                entity.setPassword(dto.getPassword());
                entity.setFailure(dto.getFailure());
                entity.setMerchantId(currLoginUser.getHigherid());
                importErrorService.create(entity);
            }
        }

    	successCount = successCount - failNum;
    	failCount = failCount + failNum;
    	int total = successCount+failCount; //总条数
    	log.debug("失败条数==="+failCount+"; 成功条数==="+successCount+"; 总条数==="+total);

		returnMap.put("result", ResultEnum.SUCCESS.getNo());
    	StringBuffer msg = new StringBuffer();
    	msg.append("批量导入完成，共").append(total)
    	   .append("条,成功导入").append(successCount)
    	   .append("条，失败").append(failCount).append("条");
		returnMap.put("msg",msg.toString());
    	if(failList.size() > 0){
			returnMap.put("data",uuid);
    	}
		
		return returnMap;
	}

	@Override
	public Map<String, String> uploadCooOrgExcel(Long userId, String domain, InputStream file, String fileName) throws Exception {
		Map<String,String> returnMap = new HashMap<>(16);
		//读取Excel数据
        List<ImportCooOrgDto> list = readCooOrgExcel(file, fileName);
        List<ImportCooOrgDto> failList = new ArrayList();
        Set<String> refreshCompanyIds = new HashSet<>();
        //初始化统计数据
        int total = list.size();
        int successCount = 0;
        int failCount = list.size();
        //失败数据批次号
        String uuid = UUID.randomUUID().toString();

        //公司名称、地址、省份不能为空   姓名、手机号不能为空  初始密码x1234567  状态：在用
        if (CollectionUtils.isNotEmpty(list)) {
            Map<String,List<ImportCooOrgDto>> unrepeatData = new LinkedHashMap<>(16);
            Set<String> companyNameSet = new LinkedHashSet<>();
            for (ImportCooOrgDto importCooOrgDto : list) {
                if (StringUtils.isNotBlank(importCooOrgDto.getCompanyName()) && StringUtils.isNotBlank(importCooOrgDto.getAddress())
                        && StringUtils.isNotBlank(importCooOrgDto.getProvince())) {
                    companyNameSet.add(importCooOrgDto.getCompanyName());
                }else {
                    importCooOrgDto.setFailMsg("公司名称、地址、省份不能为空");
                    failList.add(importCooOrgDto);
                }
            }
            for (String companyName : companyNameSet) {
            	int isAdminNum = 0;
            	int isPrincipal = 0;
                List<ImportCooOrgDto> orgDtoList = new ArrayList<>();
                for (ImportCooOrgDto importCooOrgDto : list) {
                    if (companyName.equals(importCooOrgDto.getCompanyName())) {
						if (StringUtils.isNotBlank(importCooOrgDto.getIsAdmin()) && "是".equals(importCooOrgDto.getIsAdmin())) {
							isAdminNum++;
							if (isAdminNum > 1) {
								importCooOrgDto.setIsAdmin("否");
							}
						}
						if(StringUtils.isNotBlank(importCooOrgDto.getPrincipalPhone())){
							isPrincipal++;
							if(isPrincipal > 1){
								importCooOrgDto.setPrincipalName(StringUtils.EMPTY);
								importCooOrgDto.setPrincipalPhone(StringUtils.EMPTY);
							}
						}
                        orgDtoList.add(importCooOrgDto);
                    }
                }
                unrepeatData.put(companyName, orgDtoList);
            }

            //当前登录用户
            PartnerInfo currLoginUser = partnerInfoService.selectById(userId);
            if (currLoginUser != null && currLoginUser.getHigherid() != null && currLoginUser.getHigherid() != 0) {
                for (String companyName : unrepeatData.keySet()) {
                    CompanyRelationDto companyRelationDto = new CompanyRelationDto();
                    companyRelationDto.setAgentCompanyName(companyName);
                    companyRelationDto.setDevCompany(currLoginUser.getHigherid());
                    List<CompanyRelationDto> relationDtoList = companyRelationService.selectDtoListByDto(companyRelationDto);
					List<ImportCooOrgDto> importCooOrgDtoList = unrepeatData.get(companyName);
                    if (relationDtoList != null && relationDtoList.size() > 0) {
                    	// 合作机构存在
                        DepartmentDto departmentDto = new DepartmentDto();
                        departmentDto.setMerchantId(relationDtoList.get(0).getAgentCompany());
                        departmentDto.setParentId(0L);
                        Department department = departmentDao.selectByDto(departmentDto);
                        // 校验合作机构是否存在渠道经理.不存在可更新成新的渠道经理
						if(Objects.isNull(department.getPrincipal())){
							ImportCooOrgDto importCooOrgDto = importCooOrgDtoList.stream().filter(item -> StringUtils.isNotBlank(item.getPrincipalPhone()))
									.findFirst().orElse(null);
							if(Objects.nonNull(importCooOrgDto)){
								String principalPhone = importCooOrgDto.getPrincipalPhone();
								PartnerInfoDto partnerInfoDto = new PartnerInfoDto();
								partnerInfoDto.setPhone(principalPhone);
								PartnerInfo partnerInfo = partnerInfoService.selectByDto(partnerInfoDto);
								if(Objects.nonNull(partnerInfo)){
									departmentDto = new DepartmentDto();
									departmentDto.setId(department.getId());
									departmentDto.setPrincipal(partnerInfo.getId());
									departmentDao.update(departmentDto);
								}
							}
						}
                        addStoreInfoForImportCooOrg(
                        		failList,
								importCooOrgDtoList,
								department.getId(),
								relationDtoList.get(0).getAgentCompany(),
								currLoginUser.getId(),
								domain);
						// 分销公司集合,用于导入完成后的部门层级刷新
						refreshCompanyIds.add(String.valueOf(department.getMerchantId()));
                    }else {
                        ImportCooOrgDto importCooOrgDto = unrepeatData.get(companyName).get(0);
                        //该开发商下无此公司   进行添加操作
                        DepartmentDto departmentDto = new DepartmentDto();
                        departmentDto.setDepartmentName(importCooOrgDto.getCompanyName());
                        departmentDto.setAddress(importCooOrgDto.getAddress());
                        AreaDto province = new AreaDto();
                        province.setName(importCooOrgDto.getProvince());
                        // 排除直辖市省和市同名
                        province.setSql(" parent is null");
                        Area parea = areaService.selectByDto(province);
                        if (parea != null)
                            departmentDto.setProvince(parea.getId());
                        if (StringUtils.isNotBlank(importCooOrgDto.getCity())) {
                            AreaDto city = new AreaDto();
                            city.setName(importCooOrgDto.getCity());
                            // 排除直辖市省和市同名
                            city.setSql(" parent is not null");
                            Area carea = areaService.selectByDto(city);
                            if (carea != null)
                                departmentDto.setCity(carea.getId());
                        }

						ImportCooOrgDto principalDto = importCooOrgDtoList.stream().filter(item -> StringUtils.isNotBlank(item.getPrincipalPhone()))
								.findFirst().orElse(null);
						if(Objects.nonNull(principalDto)){
							String principalPhone = principalDto.getPrincipalPhone();
							PartnerInfoDto partnerInfoDto = new PartnerInfoDto();
							partnerInfoDto.setPhone(principalPhone);
							PartnerInfo partnerInfo = partnerInfoService.selectByDto(partnerInfoDto);
							if(Objects.nonNull(partnerInfo)){
								departmentDto.setPrincipal(partnerInfo.getId());
							}
						}

                        Map<String, String> resultMap = addAgentDept(departmentDto, currLoginUser.getHigherid(),currLoginUser.getId());
                        if (ResultEnum.FAIL.getNo().equals(resultMap.get("result"))) {
                            importCooOrgDto.setFailMsg("新增公司异常");
                            failList.add(importCooOrgDto);
                        }else {
							// 分销公司集合,用于导入完成后的部门层级刷新
							refreshCompanyIds.add(resultMap.get("companyId"));
                            addStoreInfoForImportCooOrg(failList, importCooOrgDtoList, Long.valueOf(resultMap.get("deptId"))
                                    ,Long.valueOf(resultMap.get("companyId")),currLoginUser.getId(),domain);
                        }
                    }
                }

                //导入失败数据
                for (ImportCooOrgDto dto : failList) {
                    ImportErrorDto entity = new ImportErrorDto();
                    entity.setImpId(uuid);
                    entity.setImportType("1");
                    entity.setUnitName(dto.getCompanyName());
                    entity.setUnitName2(dto.getStoreName());
                    entity.setUnitName3(dto.getGroupName());
                    entity.setAddress(dto.getAddress());
                    entity.setProvince(dto.getProvince());
                    entity.setCity(dto.getCity());
                    entity.setUserName(dto.getUserName());
                    entity.setUserPhone(dto.getPhone());
                    entity.setIsAdmin(dto.getIsAdmin());
                    entity.setFailure(dto.getFailMsg());
                    entity.setMerchantId(currLoginUser.getHigherid());
                    importErrorService.create(entity);
                }

                failCount = failList.size();
                successCount = total - failCount;
            }else {
                log.info("未获取到商户信息");
            }
        }



		StringBuilder msg = new StringBuilder();
		msg.append("批量导入完成，共").append(total)
				.append("条,成功导入").append(successCount)
				.append("条，失败").append(failCount).append("条");
		returnMap.put("refreshCompanyIds", StringUtils.join(refreshCompanyIds.toArray(new String[refreshCompanyIds.size()]), ","));
		returnMap.put("result", ResultEnum.SUCCESS.getNo());
		returnMap.put("msg",msg.toString());
		if(failList.size() > 0){
			returnMap.put("data",uuid);
		}
		return returnMap;
	}

	private void addStoreInfoForImportCooOrg(List<ImportCooOrgDto> failList,List<ImportCooOrgDto> dtoList,Long compnayId,Long higherId,Long currLoginUserId,String domain) {
        Map<String,List<ImportCooOrgDto>> unrepeatData = new LinkedHashMap<>(16);
	    Set<String> storeNameSet = new LinkedHashSet<>();
	    for (ImportCooOrgDto importCooOrgDto : dtoList) {
            if (StringUtils.isNotBlank(importCooOrgDto.getStoreName())) {
                storeNameSet.add(importCooOrgDto.getStoreName());
            } else {
				addUserForImportCooOrg(failList, importCooOrgDto,higherId,compnayId,domain);
			}
        }
        if (storeNameSet != null && storeNameSet.size() > 0) {
            for (String s : storeNameSet) {
                List<ImportCooOrgDto> orgDtoList = new ArrayList<>();
                for (ImportCooOrgDto importCooOrgDto : dtoList) {
                    if (s.equals(importCooOrgDto.getStoreName())) {
                        orgDtoList.add(importCooOrgDto);
                    }
                }
                unrepeatData.put(s,orgDtoList);
            }
        }

        if (unrepeatData != null && unrepeatData.size() > 0) {
            for (String storeName : unrepeatData.keySet()) {
                DepartmentDto dpt = new DepartmentDto();
                dpt.setParentId(compnayId);
                dpt.setDepartmentName(storeName);
                Department department = departmentDao.selectByDto(dpt);
                if (department != null) {
                    addGroupInfoForImportCooOrg(failList,unrepeatData.get(storeName),higherId,department.getId(),currLoginUserId,domain);
                }else {
                    DepartmentDto departmentDto = new DepartmentDto();
                    departmentDto.setParentId(compnayId);
                    departmentDto.setDepartmentName(storeName);
                    Map<String, String> resultMap = addAgentDept(departmentDto, compnayId,currLoginUserId);
                    if (ResultEnum.SUCCESS.getNo().equals(resultMap.get("result"))) {
                        addGroupInfoForImportCooOrg(failList,unrepeatData.get(storeName),higherId,Long.valueOf(resultMap.get("deptId")),currLoginUserId,domain);
                    }
                }
            }
        }

    }

    private void addGroupInfoForImportCooOrg(List<ImportCooOrgDto> failList,List<ImportCooOrgDto> dtoList,Long higherId,Long deptId,Long currLoginUserId,String domain) {
        Map<String,List<ImportCooOrgDto>> unrepeatData = new LinkedHashMap<>(16);
        Set<String> groupNameSet = new LinkedHashSet<>();
        for (ImportCooOrgDto importCooOrgDto : dtoList) {
            if (StringUtils.isNotBlank(importCooOrgDto.getGroupName())) {
                groupNameSet.add(importCooOrgDto.getGroupName());
            }else {
                addUserForImportCooOrg(failList, importCooOrgDto,higherId,deptId,domain);
            }
        }
        if (groupNameSet != null && groupNameSet.size() > 0) {
            for (String s : groupNameSet) {
                List<ImportCooOrgDto> orgDtoList = new ArrayList<>();
                for (ImportCooOrgDto importCooOrgDto : dtoList) {
                    if (s.equals(importCooOrgDto.getGroupName())) {
                        orgDtoList.add(importCooOrgDto);
                    }
                }
                unrepeatData.put(s,orgDtoList);
            }
        }


        if (unrepeatData != null && unrepeatData.size() > 0) {
            for (String s : unrepeatData.keySet()) {
                List<ImportCooOrgDto> list = unrepeatData.get(s);
                for (ImportCooOrgDto importCooOrgDto : list) {
                    DepartmentDto gro = new DepartmentDto();
                    gro.setParentId(deptId);
                    gro.setDepartmentName(importCooOrgDto.getGroupName());
                    Department group = departmentDao.selectByDto(gro);
                    //组别
                    if (group != null) {
                        addUserForImportCooOrg(failList, importCooOrgDto, higherId, group.getId(), domain);
                    } else {
                        DepartmentDto departmentDto = new DepartmentDto();
                        departmentDto.setParentId(deptId);
                        departmentDto.setDepartmentName(importCooOrgDto.getGroupName());
                        Map<String, String> resultMap = addAgentDept(departmentDto, higherId, currLoginUserId);
                        if (ResultEnum.FAIL.getNo().equals(resultMap.get("result"))) {
                            importCooOrgDto.setFailMsg("新增组别异常");
                            failList.add(importCooOrgDto);
                        } else {
                            addUserForImportCooOrg(failList, importCooOrgDto, higherId, Long.valueOf(resultMap.get("deptId")), domain);
                        }
                    }
                }
            }
        }
	}

	private void addUserForImportCooOrg(List<ImportCooOrgDto> failList,ImportCooOrgDto importCooOrgDto,Long higherId,Long deptId,String domain) {
        if (StringUtils.isNotBlank(importCooOrgDto.getUserName()) && StringUtils.isNotBlank(importCooOrgDto.getPhone())) {
            PartnerInfoDto pi = new PartnerInfoDto();
			pi.setPhone(importCooOrgDto.getPhone());
			List<Long> companyIds = partnerInfoService.listGroupCompanyId(domain, null);
            pi.setCompanyIds(companyIds);
            List<PartnerInfo> partnerInfoList = partnerInfoService.checkRepeat(pi);
            if (partnerInfoList != null && partnerInfoList.size() > 0) {
                importCooOrgDto.setFailMsg("手机号已存在");
                failList.add(importCooOrgDto);
            } else {
                PartnerInfoDto partnerInfoDto = new PartnerInfoDto();
                partnerInfoDto.setHigherid(higherId);
                partnerInfoDto.setDelFlag(DelFlagEnum.UNDEL.getNo());
                List<PartnerInfo> piList = partnerInfoService.selectListByDto(partnerInfoDto);
                PartnerInfoDto partnerInfoDto1 = new PartnerInfoDto();
                if (piList != null && piList.size() > 0) {
                    //判断是否已存在管理员，如果已存在则把导入的员工都设为个人账号。不存在则把第一次遇到的数据为管理员的设为管理员后面的数据都为个人账号
                    partnerInfoDto1.setAccountType(PartnerInfoEnum.ACCOUNT_MECHANISM.getCode());
                    for (PartnerInfo partnerInfo : piList) {
						if (PartnerInfoEnum.ACCOUNT_MECHANISM.getCode().equals(partnerInfo.getAccountType())) {
							partnerInfoDto1.setAccountType(PartnerInfoEnum.ACCOUNT_INDIVIDUAL.getCode());
							break;
						}
					}
                } else {
                    if (StringUtils.isNotBlank(importCooOrgDto.getIsAdmin())) {
                        if ("是".equals(importCooOrgDto.getIsAdmin())) {
                            partnerInfoDto1.setAccountType(PartnerInfoEnum.ACCOUNT_MECHANISM.getCode());
                        }else {
							partnerInfoDto1.setAccountType(PartnerInfoEnum.ACCOUNT_INDIVIDUAL.getCode());
						}
                    }else {
                        partnerInfoDto1.setAccountType(PartnerInfoEnum.ACCOUNT_INDIVIDUAL.getCode());
                    }
                }
                partnerInfoDto1.setAccountName(importCooOrgDto.getUserName());
                partnerInfoDto1.setPhone(importCooOrgDto.getPhone());
                partnerInfoDto1.setPassword(PasswordUtil.defaultPwd());
                partnerInfoDto1.setDeptId(deptId);
                partnerInfoDto1.setHigherid(higherId);
                partnerInfoDto1.setStatus(PartnerInfoEnum.STATUS_ENABLE.getCode());
                partnerInfoDto1.setCreateTime(new Date());
                partnerInfoDto1.setDelFlag(DelFlagEnum.UNDEL.getNo());
                try {
                    if(partnerInfoService.saveOrUpdate(partnerInfoDto1) <= 0) {
                        importCooOrgDto.setFailMsg("添加员工信息失败");
                        failList.add(importCooOrgDto);
                    }
                } catch (Exception e) {
                    importCooOrgDto.setFailMsg("添加员工信息失败");
                    failList.add(importCooOrgDto);
                    log.debug("添加员工信息失败",e);
                }
            }
        }else if((StringUtils.isBlank(importCooOrgDto.getUserName()) && StringUtils.isNotBlank(importCooOrgDto.getPhone()))
                || (StringUtils.isNotBlank(importCooOrgDto.getUserName()) && StringUtils.isBlank(importCooOrgDto.getPhone()))) {
            importCooOrgDto.setFailMsg("员工信息不足：姓名和手机号必填");
            failList.add(importCooOrgDto);
        }
    }

    private Map<String,String> addAgentDept(DepartmentDto reqDto,Long higherId,Long currLoginUserId) {
        Map<String,String> resultMap = new HashMap<>();
        try {
            Long merchantId = 0L;
            CompanyDto companyDto = new CompanyDto();
            DepartmentDto departmentDto = new DepartmentDto();
            if(null == reqDto.getParentId()){
                //查询其开发商的是否默认佣金设置
                Company devCompany = companyService.selectById(higherId);
                companyDto.setCompanyName(reqDto.getDepartmentName());
                companyDto.setPrincipal(reqDto.getPrincipal());
                companyDto.setCompanyType(1L);
                companyDto.setAdress(reqDto.getAddress());
                companyDto.setProvince(reqDto.getProvince());
                companyDto.setCity(reqDto.getCity());
                companyDto.setDelFlag(DelFlagEnum.UNDEL.getNo());
                companyDto.setCommissionFlag(devCompany.getCommissionFlag());
                companyDto.setMessageFlag(devCompany.getMessageFlag());
                companyDto.setStatus(CheckStatusEnum.SUCCESS.getNo());
                companyDto.setCreateTime(Calendar.getInstance().getTime());
                companyDto.setCreateUserId(currLoginUserId);
                companyDto = companyService.create(companyDto);
                resultMap.put("companyId",companyDto.getId().toString());
                //更新新增机构的公司代码
                try {
                    Company company = new Company();
                    BeanUtils.copyProperties(companyDto, company);
                    String companyCode = companyService.getCompanyCode(company);
                    CompanyDto dto = new CompanyDto();
                    dto.setId(company.getId());
                    dto.setCompanyCode(companyCode);
                    companyService.updateByMyid(dto);
                } catch (Exception e) {
                    log.error("更新公司代码失败：{}",e);
                }

                merchantId = companyDto.getId();

                //新增一级机构时，向楼盘关系表中绑定默认全部楼盘
                insertPremisesRelation(higherId,companyDto.getId());

                //合作机构与开发商绑定
                CompanyRelationDto crDto = new CompanyRelationDto();
                crDto.setDevCompany(higherId);
                crDto.setAgentCompany(merchantId);
                crDto.setRelationStatus(RelationStatusEnum.VALID.getNo());
                crDto.setDelFlag(DelFlagEnum.UNDEL.getNo());
                crDto.setCreateUserId(currLoginUserId);
                companyRelationService.create(crDto);

				departmentDto.setParentId(0L);
				departmentDto.setDepartmentCode(String.valueOf(merchantId));
            }else {
                Department department = departmentDao.selectById(reqDto.getParentId());
                merchantId = department.getMerchantId();
                departmentDto.setParentId(reqDto.getParentId());
            }

            //开发商后台新增部门
            departmentDto.setDepartmentName(reqDto.getDepartmentName());
            departmentDto.setMerchantId(merchantId);
            departmentDto.setPrincipal(reqDto.getPrincipal());
            departmentDto.setDelFlag(DelFlagEnum.UNDEL.getNo());
            departmentDto.setCreateUserId(currLoginUserId);
            departmentDto.setCreateTime(Calendar.getInstance().getTime());
            departmentDto = departmentDao.create(departmentDto);
            resultMap.put("result",ResultEnum.SUCCESS.getNo());
            resultMap.put("deptId",departmentDto.getId().toString());
        }catch (Exception ex) {
            ex.printStackTrace();
            resultMap.put("result",ResultEnum.FAIL.getNo());
        }
        return resultMap;
    }

    private void insertPremisesRelation(Long devCompanyId, Long agentCompanyId) {
        // 查询该开发商下的所有楼盘
        PremisesDto pDto = new PremisesDto();
        pDto.setDelFlag(DelFlagEnum.UNDEL.getNo());
        pDto.setCompanyId(devCompanyId);
        List<Premises> lists = premisesService.selectListByDto(pDto);
        for (Premises premises : lists) {
            // 在楼盘合作关系表中绑定合作机构和楼盘信息
            PremisesRelationDto relationDto = new PremisesRelationDto();
            relationDto.setDelFlag(DelFlagEnum.UNDEL.getNo());
            relationDto.setDevCompanyId(devCompanyId);
            relationDto.setAgentCompanyId(agentCompanyId);
            relationDto.setPremisesId(premises.getId());
            relationDto.setStatus(MechanismStatusEnum.enable.getNo());
            premisesRelationService.create(relationDto);
        }
    }

	/**
	 * 获取excel数据
	 */
	public Map<String, List<PartnerInfoDto>> readUser(InputStream file, String fileType){
		Map<String, List<PartnerInfoDto>> map = new HashMap<String, List<PartnerInfoDto>>();
		//保存正确的数据
    	List<PartnerInfoDto> list = new ArrayList<PartnerInfoDto>();
    	//保存问题数据
    	List<PartnerInfoDto> failList = new ArrayList<PartnerInfoDto>();
		
		try {
			Workbook wb = null;
			if (fileType.equals("xls")) {
				wb = new HSSFWorkbook(file);
			} else if (fileType.equals("xlsx")) {
				wb = new XSSFWorkbook(file);
			} else {
				log.debug("您上传的excel格式不正确");
			}
			Sheet sheet1 = wb.getSheetAt(0);
			log.debug("excel上传行数===="+sheet1.getLastRowNum());
			
			Map<String, String> phoneMap = new HashMap<String, String>(); //用于判断excel中的号码是否重复
			for (int i = 1; i <= sheet1.getLastRowNum(); i++) {
				Row row = sheet1.getRow(i);
				PartnerInfoDto dto = new PartnerInfoDto();
				String failMsg = "";
				
				//部门名称
				if (row.getCell(0) != null) {
					dto.setAccountName(row.getCell(0).toString());
				}else{
					failMsg += "姓名不能为空;";
				}
				//联系电话
				if (row.getCell(1) != null) {
					Cell cell = row.getCell(1);
					cell.setCellType(Cell.CELL_TYPE_STRING);
					String phone = cell.getStringCellValue();
					if(StringUtils.isNotBlank(phoneMap.get(phone))){
						failMsg += "联系电话重复；";
					}else{
						if (!PatternUtil.isMobile(phone.trim())
								|| !PatternUtil.isPhone(phone)) {
							failMsg += "电话号码格式错误；";
						}
					}
					dto.setPhone(phone);
					phoneMap.put(phone, phone);
				} else {
					failMsg += "联系电话不能为空;";
				}
				//部门名称
				if (row.getCell(2) != null) {
					dto.setUnitName(row.getCell(2).toString());
				}else{
					failMsg += "部门不能为空;";
				} 
				//角色名称
				if (row.getCell(3) != null){
					dto.setRoleName(row.getCell(3).toString());
				}else{
					failMsg += "角色不能为空;";
				}
				//设置密码
				if (row.getCell(4) != null){
					Cell cell = row.getCell(4);
					cell.setCellType(Cell.CELL_TYPE_STRING);
					String pwd = cell.getStringCellValue().trim();
					if (StringUtils.isNotBlank(pwd)) {
					    dto.setPassword(pwd);
                    } else {
                        failMsg += "密码不能为空;";
                    }
				}else{
					failMsg += "密码不能为空;";
				} 

				if (!"".equals(failMsg)) {
					log.debug("excel错误信息==="+failMsg);
					dto.setFailure(failMsg);
					failList.add(dto);
				} else {
					list.add(dto);
				}
			}
			
			map.put("success", list);
	        map.put("fail", failList);
		} catch (Exception e) {
			log.error("获取excel数据失败: {}", e);
		}finally{
			try {
				file.close();
			} catch (IOException e) {
				log.error("isExistsInM2 error: {}",e);
			}
		}
        
        return map;
	}

	/**
	 * 查询手机号是否重复
	 * @param list
	 * @return
	 */
	public List<PartnerInfo> distinctPhoneNum(List<PartnerInfoDto> list, Long merchantId){

		if (list == null || list.size() == 0) {
			return null;
		}
		List<String> phones = Lists.newArrayList();
		for (PartnerInfoDto partnerInfoDto : list) {
			phones.add(partnerInfoDto.getPhone());
		}
		PartnerInfoDto entity = new PartnerInfoDto();
		entity.setPhoneList(phones);
		List<Long> companyIds = partnerInfoService.listGroupCompanyIdByCompanyId(merchantId, true);
		entity.setCompanyIds(companyIds);
		List<PartnerInfo> parList = partnerInfoService.checkRepeat(entity);
		return parList;
	}
	
	/**
	 * 获取公司下所有部门
	 */
	public Map<String, Long> allOrgMap(List<PartnerInfoDto> list, Long merchantId){
		Map<String, Long> map = new HashMap<String, Long>();
		
		List<String> departmentNames = new ArrayList<String>();
		for(PartnerInfoDto dto : list){
			departmentNames.add(dto.getUnitName());
		}
		DepartmentDto entity = new DepartmentDto();
		entity.setDepartmentNames(departmentNames);
		entity.setMerchantId(merchantId);
		List<Department> depList = departmentDao.distinctOrgNum(entity);
		for(Department dep : depList){
			map.put(dep.getDepartmentName(), dep.getId());
		}
		return map;
	}
	
	/**
	 * 获取公司下所有角色
	 */
	public Map<String, Long> allRoleMap(List<PartnerInfoDto> list, Long merchantId){
		Map<String, Long> map = new HashMap<String, Long>();
		
		List<String> roleNameList = new ArrayList<String>();
		for(PartnerInfoDto dto : list){
			roleNameList.add(dto.getRoleName());
		}
		ChannelRoleDto entity = new ChannelRoleDto();
		entity.setRoleNames(roleNameList);
		entity.setMerchantId(merchantId);
		entity.setRoleOrigin(RoleOriginEnum.CHANNEL.getNo());
		List<ChannelRole> roleList = channelRoleService.selectListbySql(entity);
		for(ChannelRole role : roleList){
			map.put(role.getRoleName(), role.getId());
		}
		return map;
	}

	@Override
	public Map<String, String> uploadExcelPremise(String companyUuid, Long userId, InputStream file, String fileType) {
		OtherCompanyDto otherCompany = new OtherCompanyDto();
		otherCompany.setIsDeleted(DelFlagEnum.UNDEL.getNo());
		otherCompany.setCompanyUuid(companyUuid);
		OtherCompany oc = otherCompanyDao.selectByDto(otherCompany);
		Map<String,String> returnMap = new HashMap<String, String>();
		Map<String, List<PremisesDto>> resMap = readPremisesDto(file, fileType);
		if(! resMap.containsKey("success") && resMap.containsKey("fail")){
			returnMap.put("result","0");
			returnMap.put("msg","excel获取数据失败");
			return returnMap;
		}
		//当前登录用户
		PartnerInfo currLoginUser = partnerInfoService.selectById(userId);
		//保存正确的数据
		List<PremisesDto> list =  resMap.get("success");
		//保存问题数据
		List<PremisesDto> failList = resMap.get("fail");
		log.debug("excel导入成功Success，准备插入数据库");

		int successCount = CollectionUtils.isEmpty(list) ? 0 : list.size(); //成功条数
		int failCount = CollectionUtils.isEmpty(failList) ? 0 : failList.size(); //失败条数
		int total = successCount+failCount; //总条数
		log.debug("失败条数==="+failCount+"; 成功条数==="+successCount+"; 总条数==="+total);

		for(int i = 0; i < list.size(); i++){
			PremisesDto dto = (PremisesDto)list.get(i);
			dto.setFromSource(FromSourceEnum.FIRST.getNo());
			dto.setVisitProtectSwitch(VisitProtectSwitchEnum.CLOSE.getCode());
			dto.setCompanyId(currLoginUser.getHigherid());
			// 报备保护时间类型 4 不需要
			dto.setReportProtectType(4L);
			//允许重复报备：0 关
			dto.setRepeatReportFlag(0);
			dto.setRepeatDelayDealTime(0);
			dto.setCloseProtectMachine("0");
			//交易延长时间设置 不需要
			dto.setSaveDaysType(Integer.parseInt(TimeUnitTypeEnum.NONE.getNo()));
			PremisesDto addDto = premisesService.create(dto);
			dto.setId(addDto.getId());
			PremisesRelationExternalDto pre = new PremisesRelationExternalDto();
			pre.setAgentCompanyId(oc.getChannelCompanyId());
			pre.setExternalCompanyId(oc.getCompanyUuid());
			pre.setPremisesId(dto.getId());
			pre.setExternalId(dto.getEstateUuid());
			pre.setSysId(ExternalSysEnum.V10.getCode());
			premisesRelationExternalService.create(pre);
		}

		//导入失败数据
		String uuid = UUID.randomUUID().toString();
		for(int i = 0; i < failList.size(); i++){
			PremisesDto dto = (PremisesDto)failList.get(i);
			ImportErrorDto entity = new ImportErrorDto();
			entity.setImpId(uuid);
			entity.setImportType("1");
			entity.setUnitName(dto.getName());
			entity.setUnitCode(dto.getEstateUuid());
			entity.setRemark(dto.getRemark());
			entity.setFailure(dto.getErrorMsg());
			entity.setMerchantId(oc.getChannelCompanyId());
			importErrorService.create(entity);
		}
		returnMap.put("result", ResultEnum.SUCCESS.getNo());
		StringBuffer msg = new StringBuffer();
		msg.append("批量导入完成，共").append(total)
				.append("条,成功导入").append(successCount)
				.append("条，失败").append(failCount).append("条");
		returnMap.put("msg",msg.toString());
		if(failList.size() > 0){
			returnMap.put("data",uuid);
		}

		return returnMap;
	}

	/**
	 * 获取excel数据
	 */
	public Map<String, List<PremisesDto>> readPremisesDto(InputStream file, String fileType){

		Map<String, List<PremisesDto>> map = new HashMap<String, List<PremisesDto>>();
		//保存正确的数据
		List<PremisesDto> list = new ArrayList<PremisesDto>();
		//保存问题数据
		List<PremisesDto> failList = new ArrayList<PremisesDto>();

		try {
			Workbook wb = null;
			if (fileType.equals("xls")) {
				wb = new HSSFWorkbook(file);
			} else if (fileType.equals("xlsx")) {
				wb = new XSSFWorkbook(file);
			} else {
				log.info("您上传的excel格式不正确");
			}
			Sheet sheet1 = wb.getSheetAt(0);
			log.info("excel上传行数===="+sheet1.getLastRowNum());

			Map<String, String> orgEstateNameMap = new HashMap<String, String>(); //用于判断excel中的楼盘名称是否重复
			for (int i = 1; i <= sheet1.getLastRowNum(); i++) {
				Row row = sheet1.getRow(i);
				PremisesDto dto = new PremisesDto();
				String failMsg = "";

				//楼盘名称
				if (row.getCell(1) != null) {
					Cell cell = row.getCell(1);
					cell.setCellType(Cell.CELL_TYPE_STRING);
					String orgEstateName = cell.getStringCellValue();
					if(StringUtils.isNotBlank(orgEstateNameMap.get(orgEstateName))){
						failMsg += "楼盘名称重复；";
					}
					dto.setName(orgEstateName);
					orgEstateNameMap.put(orgEstateName, orgEstateName);
				} else {
					failMsg += "楼盘名称不能为空;";
				}
				//楼盘UUID
				if (row.getCell(0) != null) {
					dto.setEstateUuid(row.getCell(0).toString());
				}
				//楼盘地址
				if (row.getCell(2) != null){
					dto.setPremisesAddress(row.getCell(2).toString());
				}
				//省
				if (row.getCell(3) != null){
					dto.setProvinceId(Long.parseLong(numberFormat(row.getCell(3))));
				}
				//市
				if (row.getCell(4) != null){
					dto.setCityId(Long.parseLong(numberFormat(row.getCell(4))));
				}
				//区
				if (row.getCell(5) != null){
					dto.setAreaId(Long.parseLong(numberFormat(row.getCell(5))));
				}
				//均价
				if (row.getCell(6) != null){
					String ap = numberFormat(row.getCell(6));
					dto.setAveragePrice(ap+","+ap);
				}
				//建筑类型
				if (row.getCell(7) != null){
					dto.setBuildingType(numberFormat(row.getCell(7)));
				}
				//现金奖
				if (row.getCell(8) != null){
					dto.setDealAward(numberFormat(row.getCell(8)));
				}
				//结佣方式
				if (row.getCell(9) != null){
					dto.setCommissionWay(numberFormat(row.getCell(9)));
				}
				//合同有效开始日期
				if (row.getCell(10) != null){
					log.info(row.getCell(10).toString()+ StringConstant.FOUR_STAR + getCellValueByCell(row.getCell(10)));
					dto.setCooperationBeginTime(DateUtils.StringToDate(getCellValueByCell(row.getCell(10)), DateStyle.YYYY_MM_DD));
				}
				//合同有效结束日期
				if (row.getCell(11) != null){
					log.info(row.getCell(11).toString()+ StringConstant.FOUR_STAR + getCellValueByCell(row.getCell(11)));
					dto.setCooperationEndTime(DateUtils.StringToDate(getCellValueByCell(row.getCell(11)), DateStyle.YYYY_MM_DD));
				}
				// 合作规则
				if (row.getCell(12) != null){
					dto.setCooperationRule(numberFormat(row.getCell(12)));
				}
				// 开发商
				if (row.getCell(13) != null){
					dto.setBuilderCompany(row.getCell(13).toString());
				}
				// 地下车位
				if (row.getCell(14) != null){
					dto.setDownCarPort(numberFormat(row.getCell(14)));
				}
				// 项目描述
				if (row.getCell(15) != null){
					dto.setIntroduction(row.getCell(15).toString());
				}
				// 项目特色
				if (row.getCell(16) != null){
					dto.setCodeName(row.getCell(16).toString());
				}
				// 绿化率
				if (row.getCell(17) != null){
					dto.setGreeningRate(numberFormat(row.getCell(17)));
				}
				// 是否主推
				if (row.getCell(18) != null){
					if("0".equals(numberFormat(row.getCell(18)))){
						dto.setMainPush(1);
					}else{
						dto.setMainPush(2);
					}
				}
				// 规划户数
				if (row.getCell(19) != null){
					dto.setHouseHolds(numberFormat(row.getCell(19)));
				}
				// 报备保护天数类型 以前为分钟
				if (row.getCell(20) != null){
					dto.setVisitProtectType("4");
				}
				//报备保护时间
				if (row.getCell(21) != null){
					dto.setVisitProtect(numberFormat(row.getCell(21)));
					String visitProtect = dto.getVisitProtect();
					if("3".equals(dto.getVisitProtectType())){
						visitProtect = Integer.toString(Integer.parseInt(dto.getVisitProtect()) * 1440);
					}else if("2".equals(dto.getVisitProtectType())){
						visitProtect = Long.toString(Integer.parseInt(dto.getVisitProtect()) * 60);
					}else if("4".equals(dto.getVisitProtectType()) || "0".equals(dto.getVisitProtectType())){
						dto.setVisitProtectType("4");
						visitProtect = "";
					}
					dto.setVisitProtect(visitProtect);
				}
				// 默认上架
				dto.setSaleStatus(ShelvesStatusEnum.type1.getNo());
				// 默认待审核
				dto.setPremisesStatus(PremisesStatusEnum.type0.getNo());
				//物业公司
				if (row.getCell(22) != null){
					dto.setPropertyCompany(row.getCell(22).toString());
				}
				//报备方式0不限，1全号，2前三后四
				if (row.getCell(23) != null){
					if("1".equals(numberFormat(row.getCell(23)))){
						dto.setReportWay(ReportWayEnum.FULL_NUMBER.getNo());
					}else if("2".equals(row.getCell(23).toString())){
						dto.setReportWay(ReportWayEnum.BEFORE_THREE_AFTER_FOUR.getNo());
					}else{
						dto.setReportWay(ReportWayEnum.UNLIMITED.getNo());
					}
				}
				//开盘日期
				if (row.getCell(24) != null){
					log.info(row.getCell(24).toString()+ StringConstant.FOUR_STAR + numberFormat(row.getCell(24)));
					dto.setOpeningTime(DateUtils.StringToDate(getCellValueByCell(row.getCell(24)), DateStyle.YYYY_MM_DD));
				}
				//容积率
				if (row.getCell(25) != null){
					dto.setPlotRatio(numberFormat(row.getCell(25)));
				}
				//物业费用
				if (row.getCell(26) != null){
					dto.setPropertyCharges(numberFormat(row.getCell(26)));
				}
				//产权年限
				if (row.getCell(27) != null){
					dto.setRightYears(numberFormat(row.getCell(27)));
				}
				//物业类型
				if (row.getCell(28) != null){
					dto.setEstateType(numberFormat(row.getCell(28)));
				}
				//备注
				if (row.getCell(29) != null){
					dto.setRemark(row.getCell(29).toString());
				}
				//销售状态
				if (row.getCell(30) != null){
					dto.setSaleStatus(switchPremisesStatus(numberFormat(row.getCell(30))));
				}
				//最短报备时间
				if (row.getCell(31) != null){
					dto.setSpecialTime(numberFormat(row.getCell(31)));
					dto.setSpecialTimeType(TimeUnitTypeEnum.MINUT.getNo());
				}else{
					dto.setSpecialTimeType(TimeUnitTypeEnum.NONE.getNo());
				}
				//地上车位
				if (row.getCell(32) != null){
					dto.setUpCarPort(numberFormat(row.getCell(32)));
				}
				//到场保护时间：天
				if (row.getCell(33) != null){
					dto.setSaveDays(numberFormat(row.getCell(33)));
					dto.setSaveDaysType(Integer.parseInt(TimeUnitTypeEnum.DAY.getNo()));
					String saveDays = dto.getSaveDays();
					if(3 == dto.getSaveDaysType()){
						saveDays = Integer.toString(Integer.parseInt(dto.getSaveDays()) * 1440);
					}else if(2 == dto.getSaveDaysType()){
						saveDays = Long.toString(Integer.parseInt(dto.getSaveDays()) * 60);
					}else if(4 == dto.getSaveDaysType() || 0 == dto.getSaveDaysType()){
						saveDays = "";
						dto.setSaveDaysType(Integer.parseInt(TimeUnitTypeEnum.NONE.getNo()));
					}

					dto.setSaveDays(saveDays);
				}else{
					dto.setSaveDaysType(Integer.parseInt(TimeUnitTypeEnum.NONE.getNo()));
				}
				// 楼盘带看奖
				if (row.getCell(34) != null){
					dto.setReward(numberFormat(row.getCell(34)));
				}
				// 交房日期
				if (row.getCell(35) != null){
					dto.setCheckInTime(DateUtils.StringToDate(getCellValueByCell(row.getCell(35)), DateStyle.YYYY_MM_DD_HH_MM_SS));
				}
				//封面图
				if (row.getCell(36) != null){
					dto.setUrl(row.getCell(36).toString());
				}
				if (!"".equals(failMsg)) {
					log.debug("excel错误信息==="+failMsg);
					dto.setErrorMsg(failMsg);
					failList.add(dto);
				} else {
					list.add(dto);
				}
			}
			map.put("success", list);
			map.put("fail", failList);
		} catch (Exception e) {
			log.error("获取excel数据失败: {}", e);
		}finally{
			try {
				file.close();
			} catch (IOException e) {
				log.error("readPremises error: {}",e);
			}
		}

		return map;
	}
	/**
	 * V10 楼盘状态转换渠道楼盘销售状态
	 * @param status
	 * @return
	 */
	public String switchPremisesStatus(String status){
		Map<String,String> statusMap = new HashMap<String,String>(){
			{
				put("1", "0");//待售
				put("3", "1");//在售
				put("6", "2");//售罄
				// 以下状态未使用
				put("2", "1");//在售期房
				put("4", "1");//认筹中
				put("5", "1");// 尾盘
			}};
		String returnStatus = statusMap.get(status);
		if(StringUtils.isEmpty(returnStatus)){
			return "";
		}
		return returnStatus;
	}

	private String numberFormat(Cell cell){
		NumberFormat numberFormat = NumberFormat.getInstance();
		// 不显示千位分割符，否则显示结果会变成类似1,234,567,890
		numberFormat.setGroupingUsed(false);
		if (cell == null) {
			return "";
		}
		String value = cell.toString();
		int i = cell.getCellType();
		if (i == 1) {//字符串类型
			return value;
		} else {
			value =numberFormat.format(cell.getNumericCellValue());
			return value;
		}
	}

	public static String getCellValueByCell(Cell cell) {
		//判断是否为null或空串
		if (cell==null || cell.toString().trim().equals("")) {
			return "";
		}
		String cellValue = "";
		int cellType=cell.getCellType();
		switch (cellType) {
			case Cell.CELL_TYPE_NUMERIC: // 数字
				short format = cell.getCellStyle().getDataFormat();
				if (DateUtil.isCellDateFormatted(cell)) {
					SimpleDateFormat sdf = null;
					//System.out.println("cell.getCellStyle().getDataFormat()="+cell.getCellStyle().getDataFormat());
					if (format == 20 || format == 32) {
						sdf = new SimpleDateFormat("HH:mm");
					} else if (format == 14 || format == 31 || format == 57 || format == 58) {
						// 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
						sdf = new SimpleDateFormat("yyyy-MM-dd");
						double value = cell.getNumericCellValue();
						Date date = org.apache.poi.ss.usermodel.DateUtil
								.getJavaDate(value);
						cellValue = sdf.format(date);
					}else {// 日期
						sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
					}
					try {
						cellValue = sdf.format(cell.getDateCellValue());// 日期
					} catch (Exception e) {
						try {
							throw new Exception("exception on get date data !".concat(e.toString()));
						} catch (Exception e1) {
							e1.printStackTrace();
						}
					}finally{
						sdf = null;
					}
				}  else {
					BigDecimal bd = new BigDecimal(cell.getNumericCellValue());
					cellValue = bd.toPlainString();// 数值 这种用BigDecimal包装再获取plainString，可以防止获取到科学计数值
				}
				break;
			case Cell.CELL_TYPE_STRING: // 字符串
				cellValue = cell.getStringCellValue();
				break;
			case Cell.CELL_TYPE_BOOLEAN: // Boolean
				cellValue = cell.getBooleanCellValue()+"";;
				break;
			case Cell.CELL_TYPE_FORMULA: // 公式
				cellValue = cell.getCellFormula();
				break;
			case Cell.CELL_TYPE_BLANK: // 空值
				cellValue = "";
				break;
			case Cell.CELL_TYPE_ERROR: // 故障
				cellValue = "ERROR VALUE";
				break;
			default:
				cellValue = "UNKNOW VALUE";
				break;
		}
		return cellValue;
	}

	@Override
	public Map<String, String> uploadExcelPremiseHouseType(String companyUuid, InputStream file, String fileType) {
		OtherCompanyDto otherCompany = new OtherCompanyDto();
		otherCompany.setIsDeleted(DelFlagEnum.UNDEL.getNo());
		otherCompany.setCompanyUuid(companyUuid);
		OtherCompany oc = otherCompanyDao.selectByDto(otherCompany);
		Map<String,String> returnMap = new HashMap<String, String>();
		Map<String, List<ApartmentLayoutDiagramDto>> resMap = readPremisesHouseTypeDto(file, fileType);
		if(! resMap.containsKey("success") && resMap.containsKey("fail")){
			returnMap.put("result","0");
			returnMap.put("msg","excel获取数据失败");
			return returnMap;
		}
		//保存正确的数据
		List<ApartmentLayoutDiagramDto> list =  resMap.get("success");
		//保存问题数据
		List<ApartmentLayoutDiagramDto> failList = resMap.get("fail");
		log.debug("excel导入成功Success，准备插入数据库");

		int successCount = CollectionUtils.isEmpty(list) ? 0 : list.size(); //成功条数
		int failCount = CollectionUtils.isEmpty(failList) ? 0 : failList.size(); //失败条数
		int total = successCount+failCount; //总条数
		log.debug("失败条数==="+failCount+"; 成功条数==="+successCount+"; 总条数==="+total);

		for(int i = 0; i < list.size(); i++){
			ApartmentLayoutDiagramDto dto = (ApartmentLayoutDiagramDto)list.get(i);
			PremisesRelationExternalDto entity = new PremisesRelationExternalDto();
			entity.setExternalId(dto.getEstateUuid());
			entity.setIsDeleted(DelFlagEnum.UNDEL.getNo());
			entity.setExternalCompanyId(oc.getCompanyUuid());
			PremisesRelationExternal pre = premisesRelationExternalService.selectByDto(entity);
			dto.setPremisesId(pre.getPremisesId());
			apartmentLayoutDiagramService.create(dto);
		}

		//导入失败数据
		String uuid = UUID.randomUUID().toString();
		for(int i = 0; i < failList.size(); i++){
			ApartmentLayoutDiagramDto dto = (ApartmentLayoutDiagramDto)failList.get(i);
			ImportErrorDto entity = new ImportErrorDto();
			entity.setImpId(uuid);
			entity.setImportType("1");
			entity.setUnitName(dto.getRemark());
			entity.setUnitCode(dto.getEstateUuid());
			entity.setRemark(dto.getRemark());
			entity.setFailure(dto.getErrorMsg());
			entity.setMerchantId(oc.getChannelCompanyId());
			importErrorService.create(entity);
		}
		returnMap.put("result", ResultEnum.SUCCESS.getNo());
		StringBuffer msg = new StringBuffer();
		msg.append("批量导入完成，共").append(total)
				.append("条,成功导入").append(successCount)
				.append("条，失败").append(failCount).append("条");
		returnMap.put("msg",msg.toString());
		if(failList.size() > 0){
			returnMap.put("data",uuid);
		}

		return returnMap;
	}

	/**
	 * 获取excel数据
	 */
	public Map<String, List<ApartmentLayoutDiagramDto>> readPremisesHouseTypeDto(InputStream file, String fileType){

		Map<String, List<ApartmentLayoutDiagramDto>> map = new HashMap<String, List<ApartmentLayoutDiagramDto>>();
		//保存正确的数据
		List<ApartmentLayoutDiagramDto> list = new ArrayList<ApartmentLayoutDiagramDto>();
		//保存问题数据
		List<ApartmentLayoutDiagramDto> failList = new ArrayList<ApartmentLayoutDiagramDto>();

		try {
			Workbook wb = null;
			if (fileType.equals("xls")) {
				wb = new HSSFWorkbook(file);
			} else if (fileType.equals("xlsx")) {
				wb = new XSSFWorkbook(file);
			} else {
				log.info("您上传的excel格式不正确");
			}
			Sheet sheet1 = wb.getSheetAt(0);
			log.info("excel上传行数===="+sheet1.getLastRowNum());

			Map<String, String> orgEstateNameMap = new HashMap<String, String>(); //用于判断excel中的楼盘名称是否重复
			for (int i = 1; i <= sheet1.getLastRowNum(); i++) {
				Row row = sheet1.getRow(i);
				ApartmentLayoutDiagramDto dto = new ApartmentLayoutDiagramDto();
				String failMsg = "";

				//户型UUID
				if (row.getCell(1) != null) {
					Cell cell = row.getCell(1);
					cell.setCellType(Cell.CELL_TYPE_STRING);
					String orgEstateName = cell.getStringCellValue();
					if(StringUtils.isNotBlank(orgEstateNameMap.get(orgEstateName))){
						failMsg += "户型重复；";
					}
					dto.setRemark(orgEstateName);
					orgEstateNameMap.put(orgEstateName, orgEstateName);
				} else {
					failMsg += "户型UUID不能为空;";
				}
				//楼盘UUID
				if (row.getCell(0) != null) {
					dto.setEstateUuid(row.getCell(0).toString());
				}
				//面积
				if (row.getCell(2) != null){
					Float area = Float.parseFloat(numberFormat(row.getCell(2))) ;
					dto.setArea(area+","+area);
				}
				//装修类型
				if (row.getCell(3) != null){
					dto.setDecoration(numberFormat(row.getCell(3)));
				}
				//户型结构
				if (row.getCell(4) != null){
					dto.setHouseStruct(numberFormat(row.getCell(4)));
				}
				//户型名称
				if (row.getCell(5) != null){
					dto.setHuxingName(numberFormat(row.getCell(5)));
				}
				//朝向
				if (row.getCell(6) != null){
					dto.setOrientations(numberFormat(row.getCell(6)));
				}
				//均价
				if (row.getCell(7) != null){
					Integer price = Integer.parseInt(numberFormat(row.getCell(7))) ;
					dto.setAveragePrice(price+","+price);
				}
				// 户型
				String bedroom = "";
				String drawingRoom = "";
				String toilet = "";
				String veranda = "";
				//室
				if (row.getCell(8) != null){
					bedroom = numberFormat(row.getCell(8));
				}
				//厅
				if (row.getCell(9) != null){
					drawingRoom = numberFormat(row.getCell(9));
				}
				//卫
				if (row.getCell(10) != null){
					toilet = numberFormat(row.getCell(10));
				}
				//阳台
				if (row.getCell(11) != null){
					veranda = numberFormat(row.getCell(11));
				}
				dto.setApartmentLayout(buildApartmentLayout(bedroom,drawingRoom,toilet,veranda));
				//图
				if (row.getCell(12) != null){
					dto.setUrl(row.getCell(12).toString());
				}
				if (!"".equals(failMsg)) {
					log.debug("excel错误信息==="+failMsg);
					dto.setErrorMsg(failMsg);
					failList.add(dto);
				} else {
					list.add(dto);
				}
			}
			map.put("success", list);
			map.put("fail", failList);
		} catch (Exception e) {
			log.error("获取excel数据失败 {}", e);
		}finally{
			try {
				file.close();
			} catch (IOException e) {
				log.error("readPremisesHouseType error: {}",e);
			}
		}

		return map;
	}

	// 构建户型
	public String buildApartmentLayout(String bedroom,
			String drawingRoom,
			String toilet,
			String veranda){
		StringBuffer sb = new StringBuffer();
		if(StringUtils.isNotEmpty(bedroom)){
			if("0".equals(bedroom)){
				sb.append("无卧室");
			}else{
				sb.append(bedroom).append("室");
			}
		}else{
			sb.append("无卧室");
		}
		if(StringUtils.isNotEmpty(drawingRoom)){
			if("0".equals(drawingRoom)){
				sb.append("无厅");
			}else {
				sb.append(drawingRoom).append("厅");
			}
		}else{
			sb.append("无厅");
		}
		if(StringUtils.isNotEmpty(toilet)){
			if("0".equals(toilet)){
				sb.append("无卫");
			}else {
				sb.append(toilet).append("卫");
			}
		}else{
			sb.append("无卫");
		}
		if(StringUtils.isNotEmpty(veranda)){
			if("0".equals(veranda)){
				sb.append("无阳台");
			}else {
				sb.append(veranda).append("阳台");
			}
		}else{
			sb.append("无阳台");
		}
		return sb.toString();
	}

	@Override
	public Map<String, String> uploadExcelPremiseDynamic(String companyUuid, InputStream file, String fileType) {
		OtherCompanyDto otherCompany = new OtherCompanyDto();
		otherCompany.setIsDeleted(DelFlagEnum.UNDEL.getNo());
		otherCompany.setCompanyUuid(companyUuid);
		OtherCompany oc = otherCompanyDao.selectByDto(otherCompany);
		Map<String,String> returnMap = new HashMap<String, String>();
		Map<String, List<PremisesDynamicDto>> resMap = readPremisesDynamicDto(file, fileType);
		if(! resMap.containsKey("success") && resMap.containsKey("fail")){
			returnMap.put("result","0");
			returnMap.put("msg","excel获取数据失败");
			return returnMap;
		}
		//当前登录用户
		//PartnerInfo currLoginUser = partnerInfoService.selectById(userId);
		//保存正确的数据
		List<PremisesDynamicDto> list =  resMap.get("success");
		//保存问题数据
		List<PremisesDynamicDto> failList = resMap.get("fail");
		log.debug("excel导入成功Success，准备插入数据库");

		int successCount = CollectionUtils.isEmpty(list) ? 0 : list.size(); //成功条数
		int failCount = CollectionUtils.isEmpty(failList) ? 0 : failList.size(); //失败条数
		int total = successCount+failCount; //总条数
		log.debug("失败条数==="+failCount+"; 成功条数==="+successCount+"; 总条数==="+total);

		for(int i = 0; i < list.size(); i++){
			PremisesDynamicDto dto = (PremisesDynamicDto)list.get(i);
			PremisesRelationExternalDto entity = new PremisesRelationExternalDto();
			entity.setExternalId(dto.getEstateUuid());
			entity.setIsDeleted(DelFlagEnum.UNDEL.getNo());
			entity.setExternalCompanyId(oc.getCompanyUuid());
			PremisesRelationExternal pre = premisesRelationExternalService.selectByDto(entity);
			dto.setPremisesId(pre.getPremisesId());
			dto.setCanSee(true);
			PremisesDynamicDto addDto = premisesDynamicService.create(dto);
			dto.setId(addDto.getId());
			PremisesDynamicExternalDto pde = new PremisesDynamicExternalDto();
			pde.setExternalCompanyId(oc.getCompanyUuid());
			pde.setPremisesUuid(dto.getEstateUuid());
			pde.setPremisesId(dto.getPremisesId());
			pde.setDynamicId(dto.getId());
			pde.setDynamicUuid(dto.getDynamicUuid());
			pde.setSysId(ExternalSysEnum.V10.getCode());
			pde.setIsDeleted(DelFlagEnum.UNDEL.getNo());
			premisesDynamicExternalService.create(pde);
		}

		//导入失败数据
		String uuid = UUID.randomUUID().toString();
		for(int i = 0; i < failList.size(); i++){
			PremisesDynamicDto dto = (PremisesDynamicDto)failList.get(i);
			ImportErrorDto entity = new ImportErrorDto();
			entity.setImpId(uuid);
			entity.setImportType("1");
			entity.setUnitName(dto.getName());
			entity.setUnitCode(dto.getEstateUuid());
			entity.setRemark(dto.getRemark());
			entity.setFailure(dto.getErrorMsg());
			entity.setMerchantId(oc.getChannelCompanyId());
			importErrorService.create(entity);
		}
		returnMap.put("result", ResultEnum.SUCCESS.getNo());
		StringBuffer msg = new StringBuffer();
		msg.append("批量导入完成，共").append(total)
				.append("条,成功导入").append(successCount)
				.append("条，失败").append(failCount).append("条");
		returnMap.put("msg",msg.toString());
		if(failList.size() > 0){
			returnMap.put("data",uuid);
		}

		return returnMap;
	}

	/**
	 * 获取excel数据
	 */
	public Map<String, List<PremisesDynamicDto>> readPremisesDynamicDto(InputStream file, String fileType){

		Map<String, List<PremisesDynamicDto>> map = new HashMap<String, List<PremisesDynamicDto>>();
		//保存正确的数据
		List<PremisesDynamicDto> list = new ArrayList<PremisesDynamicDto>();
		//保存问题数据
		List<PremisesDynamicDto> failList = new ArrayList<PremisesDynamicDto>();

		try {
			Workbook wb = null;
			if (fileType.equals("xls")) {
				wb = new HSSFWorkbook(file);
			} else if (fileType.equals("xlsx")) {
				wb = new XSSFWorkbook(file);
			} else {
				log.info("您上传的excel格式不正确");
			}
			Sheet sheet1 = wb.getSheetAt(0);
			log.info("excel上传行数===="+sheet1.getLastRowNum());

			Map<String, String> orgEstateNameMap = new HashMap<String, String>(); //用于判断excel中的楼盘名称是否重复
			for (int i = 1; i <= sheet1.getLastRowNum(); i++) {
				Row row = sheet1.getRow(i);
				PremisesDynamicDto dto = new PremisesDynamicDto();
				String failMsg = "";

				//动态UUID
				if (row.getCell(1) != null) {
					Cell cell = row.getCell(1);
					cell.setCellType(Cell.CELL_TYPE_STRING);
					String orgEstateName = cell.getStringCellValue();
					if(StringUtils.isNotBlank(orgEstateNameMap.get(orgEstateName))){
						failMsg += "动态重复；";
					}
					dto.setDynamicUuid(orgEstateName);
					orgEstateNameMap.put(orgEstateName, orgEstateName);
				} else {
					failMsg += "动态UUID不能为空;";
				}
				//楼盘UUID
				if (row.getCell(0) != null) {
					dto.setEstateUuid(row.getCell(0).toString());
				}
				//标题
				if (row.getCell(2) != null){
					dto.setTitle(numberFormat(row.getCell(2)));
				}
				//内容
				if (row.getCell(3) != null){
					dto.setContent(numberFormat(row.getCell(3)));
				}
				//楼盘名称
				if (row.getCell(4) != null){
					dto.setPremisesName(numberFormat(row.getCell(4)));
				}
				if (!"".equals(failMsg)) {
					log.debug("excel错误信息==="+failMsg);
					dto.setErrorMsg(failMsg);
					failList.add(dto);
				} else {
					list.add(dto);
				}
			}
			map.put("success", list);
			map.put("fail", failList);
		} catch (Exception e) {
			log.error("获取excel数据失败: {}", e);
		}finally{
			try {
				file.close();
			} catch (IOException e) {
				log.error("readPremisesDynamicDto error: {}",e);
			}
		}

		return map;
	}

	@Override
	public Map<String, String> uploadExcelPremiseImage(String companyUuid, InputStream file, String fileType) {
		OtherCompanyDto otherCompany = new OtherCompanyDto();
		otherCompany.setIsDeleted(DelFlagEnum.UNDEL.getNo());
		otherCompany.setCompanyUuid(companyUuid);
		OtherCompany oc = otherCompanyDao.selectByDto(otherCompany);
		Map<String,String> returnMap = new HashMap<String, String>();
		Map<String, List<PremisesImageDto>> resMap = readPremisesImage(file, fileType);
		if(! resMap.containsKey("success") && resMap.containsKey("fail")){
			returnMap.put("result","0");
			returnMap.put("msg","excel获取数据失败");
			return returnMap;
		}
		//保存正确的数据
		List<PremisesImageDto> list =  resMap.get("success");
		//保存问题数据
		List<PremisesImageDto> failList = resMap.get("fail");
		log.debug("excel导入成功Success，准备插入数据库");

		int successCount = CollectionUtils.isEmpty(list) ? 0 : list.size(); //成功条数
		int failCount = CollectionUtils.isEmpty(failList) ? 0 : failList.size(); //失败条数
		int total = successCount+failCount; //总条数
		log.debug("失败条数==="+failCount+"; 成功条数==="+successCount+"; 总条数==="+total);

		for(int i = 0; i < list.size(); i++){
			PremisesImageDto dto = (PremisesImageDto)list.get(i);
			PremisesRelationExternalDto entity = new PremisesRelationExternalDto();
			entity.setExternalId(dto.getEstateUuid());
			entity.setIsDeleted(DelFlagEnum.UNDEL.getNo());
			entity.setExternalCompanyId(oc.getCompanyUuid());
			PremisesRelationExternal pre = premisesRelationExternalService.selectByDto(entity);
			dto.setPremisesId(pre.getPremisesId());
			dto.setCreateTime(new Date());
			dto.setIsDeleted(DelFlagEnum.UNDEL.getNo());
			premisesImageMapper.add(dto);
		}

		//导入失败数据
		String uuid = UUID.randomUUID().toString();
		for(int i = 0; i < failList.size(); i++){
			PremisesImageDto dto = (PremisesImageDto)failList.get(i);
			ImportErrorDto entity = new ImportErrorDto();
			entity.setImpId(uuid);
			entity.setImportType("1");
			entity.setUnitName(dto.getImageDesc());
			entity.setUnitCode(dto.getEstateUuid());
			entity.setRemark(dto.getImageDesc());
			entity.setFailure(dto.getErrorMsg());
			entity.setMerchantId(oc.getChannelCompanyId());
			importErrorService.create(entity);
		}
		returnMap.put("result", ResultEnum.SUCCESS.getNo());
		StringBuffer msg = new StringBuffer();
		msg.append("批量导入完成，共").append(total)
				.append("条,成功导入").append(successCount)
				.append("条，失败").append(failCount).append("条");
		returnMap.put("msg",msg.toString());
		if(failList.size() > 0){
			returnMap.put("data",uuid);
		}

		return returnMap;
	}

	/**
	 * 获取excel数据
	 */
	public Map<String, List<PremisesImageDto>> readPremisesImage(InputStream file, String fileType){

		Map<String, List<PremisesImageDto>> map = new HashMap<String, List<PremisesImageDto>>();
		//保存正确的数据
		List<PremisesImageDto> list = new ArrayList<PremisesImageDto>();
		//保存问题数据
		List<PremisesImageDto> failList = new ArrayList<PremisesImageDto>();

		try {
			Workbook wb = null;
			if (fileType.equals("xls")) {
				wb = new HSSFWorkbook(file);
			} else if (fileType.equals("xlsx")) {
				wb = new XSSFWorkbook(file);
			} else {
				log.info("您上传的excel格式不正确");
			}
			Sheet sheet1 = wb.getSheetAt(0);
			log.info("excel上传行数===="+sheet1.getLastRowNum());

			Map<String, String> orgEstateNameMap = new HashMap<String, String>(); //用于判断excel中的楼盘名称是否重复
			for (int i = 1; i <= sheet1.getLastRowNum(); i++) {
				Row row = sheet1.getRow(i);
				PremisesImageDto dto = new PremisesImageDto();
				String failMsg = "";

				//动态UUID
				if (row.getCell(1) != null) {
					Cell cell = row.getCell(1);
					cell.setCellType(Cell.CELL_TYPE_STRING);
					String orgEstateName = cell.getStringCellValue();
					if(StringUtils.isNotBlank(orgEstateNameMap.get(orgEstateName))){
						failMsg += "图片重复；";
					}
					dto.setImageDesc(orgEstateName);
					orgEstateNameMap.put(orgEstateName, orgEstateName);
				} else {
					failMsg += "图片UUID不能为空;";
				}
				//楼盘UUID
				if (row.getCell(0) != null) {
					dto.setEstateUuid(row.getCell(0).toString());
				}
				//类型
				if (row.getCell(2) != null){
					dto.setImageType(numberFormat(row.getCell(2)));
				}
				//地址
				if (row.getCell(3) != null){
					dto.setImageUrl(numberFormat(row.getCell(3)));
				}

				if (!"".equals(failMsg)) {
					log.debug("excel错误信息==="+failMsg);
					dto.setErrorMsg(failMsg);
					failList.add(dto);
				} else {
					list.add(dto);
				}
			}
			map.put("success", list);
			map.put("fail", failList);
		} catch (Exception e) {
			log.error("获取excel数据失败: {}", e);
		}finally{
			try {
				file.close();
			} catch (IOException e) {
				log.error("readPremisesImage error: {}",e);
			}
		}

		return map;
	}

	@Override
	public Map<String, String> uploadExcelPremiseCommission(String companyUuid, InputStream file, String fileType) {
		OtherCompanyDto otherCompany = new OtherCompanyDto();
		otherCompany.setIsDeleted(DelFlagEnum.UNDEL.getNo());
		otherCompany.setCompanyUuid(companyUuid);
		OtherCompany oc = otherCompanyDao.selectByDto(otherCompany);
		Map<String,String> returnMap = new HashMap<String, String>();
		Map<String, List<CommissionTypeFromV10>> resMap = readPremisesCommission(file, fileType);
		if(! resMap.containsKey("success") && resMap.containsKey("fail")){
			returnMap.put("result","0");
			returnMap.put("msg","excel获取数据失败");
			return returnMap;
		}
		//保存正确的数据
		List<CommissionTypeFromV10> list =  resMap.get("success");
		//保存问题数据
		List<CommissionTypeFromV10> failList = resMap.get("fail");
		log.debug("excel导入成功Success，准备插入数据库");

		int successCount = CollectionUtils.isEmpty(list) ? 0 : list.size(); //成功条数
		int failCount = CollectionUtils.isEmpty(failList) ? 0 : failList.size(); //失败条数
		int total = successCount+failCount; //总条数
		log.debug("失败条数==="+failCount+"; 成功条数==="+successCount+"; 总条数==="+total);

		Map<String, List<CommissionJsonDto>> rootMap = new HashMap<>();
		for (CommissionTypeFromV10 cv : list){
			CommissionJsonDto dto = new CommissionJsonDto();
			dto.setName(cv.getName());
			dto.setType(cv.getType());
			dto.setTypeValue(cv.getMoney());
			if(rootMap == null || rootMap.size() <= 0){
				List<CommissionJsonDto> rootList = new ArrayList<CommissionJsonDto>();
				rootList.add(dto);
				rootMap.put(cv.getEstateUuid(), rootList);
				continue;
			}
			List<CommissionJsonDto> dtoList = rootMap.get(cv.getEstateUuid());
			if(CollectionUtils.isEmpty(dtoList)){
				List<CommissionJsonDto> rootList = new ArrayList<CommissionJsonDto>();
				rootList.add(dto);
				rootMap.put(cv.getEstateUuid(), rootList);
			}else{
				dtoList.add(dto);
			}
		}
		rootMap.entrySet().stream().forEach(entry -> {
			PremisesRelationExternalDto entity = new PremisesRelationExternalDto();
			entity.setExternalId(entry.getKey());
			entity.setIsDeleted(DelFlagEnum.UNDEL.getNo());
			entity.setExternalCompanyId(oc.getCompanyUuid());
			PremisesRelationExternal pre = premisesRelationExternalService.selectByDto(entity);
			JSONArray json = JSONArray.fromObject(entry.getValue());
			PremisesDto premisesDto = new PremisesDto();
			premisesDto.setId(pre.getPremisesId());
			premisesDto.setCommission(json.toString());
			premisesService.update(premisesDto);
		});

		//导入失败数据
		String uuid = UUID.randomUUID().toString();
		for(int i = 0; i < failList.size(); i++){
			CommissionTypeFromV10 dto = (CommissionTypeFromV10)failList.get(i);
			ImportErrorDto entity = new ImportErrorDto();
			entity.setImpId(uuid);
			entity.setImportType("1");
			entity.setUnitName(dto.getEstateUuid());
			entity.setUnitCode(dto.getEstateUuid());
			entity.setRemark(dto.getEstateUuid());
			entity.setFailure(dto.getErrorMsg());
			entity.setMerchantId(oc.getChannelCompanyId());
			importErrorService.create(entity);
		}
		returnMap.put("result", ResultEnum.SUCCESS.getNo());
		StringBuffer msg = new StringBuffer();
		msg.append("批量导入完成，共").append(total)
				.append("条,成功导入").append(successCount)
				.append("条，失败").append(failCount).append("条");
		returnMap.put("msg",msg.toString());
		if(failList.size() > 0){
			returnMap.put("data",uuid);
		}

		return returnMap;
	}

	/**
	 * 获取excel数据
	 */
	public Map<String, List<CommissionTypeFromV10>> readPremisesCommission(InputStream file, String fileType){

		Map<String, List<CommissionTypeFromV10>> map = new HashMap<String, List<CommissionTypeFromV10>>();
		//保存正确的数据
		List<CommissionTypeFromV10> list = new ArrayList<CommissionTypeFromV10>();
		//保存问题数据
		List<CommissionTypeFromV10> failList = new ArrayList<CommissionTypeFromV10>();

		try {
			Workbook wb = null;
			if (fileType.equals("xls")) {
				wb = new HSSFWorkbook(file);
			} else if (fileType.equals("xlsx")) {
				wb = new XSSFWorkbook(file);
			} else {
				log.info("您上传的excel格式不正确");
			}
			Sheet sheet1 = wb.getSheetAt(0);
			log.info("excel上传行数===="+sheet1.getLastRowNum());

			for (int i = 1; i <= sheet1.getLastRowNum(); i++) {
				Row row = sheet1.getRow(i);
				CommissionTypeFromV10 dto = new CommissionTypeFromV10();
				String failMsg = "";

				//动态UUID
				if (row.getCell(1) != null) {
					Cell cell = row.getCell(1);
					cell.setCellType(Cell.CELL_TYPE_STRING);
					String orgEstateName = cell.getStringCellValue();
					dto.setName(orgEstateName);
				}
				//楼盘UUID
				if (row.getCell(0) != null) {
					dto.setEstateUuid(row.getCell(0).toString());
				}
				//类型
				if (row.getCell(2) != null){
					if("1".equals(numberFormat(row.getCell(2)))){
						dto.setType("%");
					}else if("2".equals(numberFormat(row.getCell(2)))){
						dto.setType("元");
					}

				}
				//数值
				if (row.getCell(3) != null){
					dto.setMoney(numberFormat(row.getCell(3)));
				}

				if (!"".equals(failMsg)) {
					log.debug("excel错误信息==="+failMsg);
					dto.setErrorMsg(failMsg);
					failList.add(dto);
				} else {
					list.add(dto);
				}
			}
			map.put("success", list);
			map.put("fail", failList);
		} catch (Exception e) {
			log.error("获取excel数据失败: {}", e);
		}finally{
			try {
				file.close();
			} catch (IOException e) {
				log.error("readPremisesCommission error: {}",e);
			}
		}

		return map;
	}
}
